home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Internet Toolkit
/
Internet Toolkit.iso
/
tutorial
/
pc_learn
/
spread.tut
< prev
next >
Wrap
Text File
|
1993-08-16
|
12KB
|
227 lines
----------------------------------------------------------------
USING SPREADSHEETS
THAT FEELING OF MONEY AND POWER
----------------------------------------------------------------
WHAT IS A SPREADSHEET?
One simple definition for a spreadsheet, is: "a storage and
calculation system which resembles a piece of graph paper." In
many popular spreadsheet programs this "electronically alive
grid" can act like a database (storage), calculator
(accountant's pad) and graph production device (graph output).
In effect the cells within the grid of this "electronic graph
paper" can add, subtract, move and manage numbers and
information.
----------------------------------------------------------------
DATABASE SPREADSHEET USE OF A SPREADSHEET - AN EXAMPLE
----------------------------------------------------------------
Study the illustration below then read the explanation which
follows:
A B C D
==========================================================
1 || | CLIENT CONTACT DATABASE | |
2 || | -------------------- | |
3 || | 1987-1988 | |
4 || | | |
5 ||NAME | DAY TEL | EVE TEL | FIRM |
6 || | | | |
7 ||Brown, Sam | 232-9090 | 111-7878 | Genl. Mega |
8 ||Happ, Sue | 687-9058 | 454-6767 | Whoops Inc |
9 ||Davis, Bob | 444-9999 | 343-0909 | Creamy Co |
10 || | | | |
11 || | | | |
Just as we could keep client names, christmas card lists, key
suppliers and inventories of goods in a book, we could also keep
items to remember in a spreadsheet as illustrated. But the power
and elegance of a spreadsheet is only partially storage and
retrieval. We can also sort the data by phone number (list of
all clients sorted by phone number or list of all clients sorted
by last name). We can also search the data (find all clients
employed by one firm). We can expand the database (add more
names) We can expand the fields - columns in the spreadsheet
(for example we could add yet more columns to the right to
include address, birthdate, product ordered, date of last order,
average dollar amount of order, date of first contact, etc).
Once these new items of data have been added, we could again
search and sort for various interesting facts (all clients in
zip code 98040 having more than $2,000 of business with us).
A spreadsheet typically refers to the boxes where information is
stored as cells. Each cell has an address. Thus in the example
cell A7 contains the name Sam Brown.
Most spreadsheets are large! Many offer 256 columns and over
8000 rows! You scroll or move around to view one screen of data
at a time. True database programs differ from spreadsheets in
how they view the information and process it, but for many
people, a spreadsheet used as a database is a simple,
understandable and very fast way of storing, searching and
sorting information. For larger databases of several thousand
items, you would probably be wiser to use a true database
product, however.
----------------------------------------------------------------
ANALYSIS & CALCULATION USE OF A SPREADSHEET - AN EXAMPLE
----------------------------------------------------------------
Study the illustration below then read the explanation which
follows:
A B C D
==========================================================
1 || | CHECKBOOK BALANCING FORM | |
2 || | -------------------- | |
3 || | Outstanding |Outstanding |
4 || | Withdrawals: |Deposits: |
5 ||Bal from | | | |
6 ||statement: | 1500 | 50 | 25 |
7 || | | 50 | 25 |
8 ||Sum of all | | 100 | 50 |
9 ||outstanding | | 200 | |
10 ||withdrawals:| 500 | 100 | |
11 || | | | |
12 ||Sum of all | | | |
13 ||outstanding | | | |
14 ||deposits: | 100 | | |
15 || | | | |
16 ||Total: | 1100 | | |
17 || | |
18 ||Register: | 1100 <--- When B16=B18, checkbook is balanced!
A spreadsheet can also do calculations. We all have balanced a
checkbook at some time. Most of us use the printed form on the
back of the statement. You fill in the boxes with a pencil, find
missing checks, post forgotten deposits and generally scratch
and erase your way to a balanced and reconciled checkbook each
month. The spreadsheet above looks and acts about the same but
does it more quickly. It adds new checks and erases errors more
quickly, and when done prints out a final copy on your printer.
When you update an entry on the form, it instantly adds the
columns to arrive at new totals. Trial and error the easy way!
The results from all numbers in column C are added and placed in
the cell at position B10. Similarly, the numbers in column D are
added and placed in cell B14. Finally, a calculation is done to
produce the result of B6+B14-B10 and placed in B16. This all
happens in about 1/4 second!
A feature common to many spreadsheets is the ability to create
graphs of great variety: bar, pie, stacked bar, line and scatter
graphs are quite commonly obtained from data placed into or
calculated by the spreadsheet.
Obviously more elaborate spreadsheet examples can be prepared
which produce projections, budgets, salary expenses, tax reports
and so forth. Just remember that the spreadsheet in its
calculation mode can do thousands of calculations quickly and
can change all cells based on one or two small changes in a few
cells. Many spreadsheets have special functions for advanced
statistical, financial, date and real estate formulas already
imbedded or hidden in the structure of the spreadsheet. Macros
(a form of multiple step programming or allowing the computer to
type many keystrokes at once) are a common feature which provide
yet further shortcuts within most spreadsheets.
Many spreadsheets also have hidden "help screens" which you can
quickly pop up into view for a refresher on points you have
forgotten. These help screens are frequently "context sensitive"
meaning you can stop while working with a spreadsheet formula
and the help screen can "sense the context" of your location and
jump immediately to a help screen on formulas!
A spreadsheet obviously demands a sense of organization.
Experienced spreadsheet users frequently suggest the following
guidelines:
* Try to create flexible spreadsheets. Using variables in your
formulas instead of fixed values allows quick and easy
substitution. A spreadsheet variable is a cell location which
contains an important number or other assumption. By using a
variable the underlying assumption can be changed without
changing all the formulas throughout the spreadsheet. Change
just one cell instead of many!
* Be consistent from spreadsheet to spreadsheet. Try to keep
items such as date the spreadsheet was created, author,
description of task, variables, special reference cells in the
same place - usually the top left corner for many users.
* Keep things simple. Separating complex formulas and
calculations into smaller intermediate steps or several cells
can illustrate the problem and process better than one large,
poorly documented formula stuffed into a single cell.
* Test the results of your spreadsheet before putting it into
use and from time to time in case of errors. Manually cross
check calculations or enter the number "one" into several cells
so you can mentally "see" that things look correct as results
total. Try to build cross checking into your spreadsheet.
* Document your spreadsheet with a clearly written purpose,
procedures and formulas. Imbed this information directly into
the spreadsheet. Describe where macros, range names, protected
cells have been placed. Note books or magazines which gave rise
to the spreadsheet.
* For those with a high degree of interest in spreadsheets,
a subscription to Lotus Magazine (mentioned in the reading
list elsewhere in PC-LEARN) is recommended. Back issues
are goldmines of information!
Some of the best Spreadsheet packages include the following.
Commercial packages:
Quattro Pro. Exceptional character based spreadsheet which runs
on modest machines and is quite full-featured and richly
complex. A PC Magazine Editors Choice.
Microsoft Works. This integrated low priced package which also
contains a database and word processor plus tutorials and other
goodies is an exceptional value for the beginner. The
spreadsheet, while not as complex as Lotus or as stunning as
Excel, is an fine workhorse which also contains a graphing
package for the small business or home office on a budget. Real
value many beginners will rarely outgrow.
Microsoft Excel. Graphics based, needs a more powerful machine
but produces stunning images with varied font and graphics
effects. Rich and satisfying.
Lotus 123. The old classic spreadsheet warrior, but satisfactory
if you don't mind paying a little more.
Excellent shareware/low cost and try before you buy spreadsheets
include:
Aseasyas. Probably the best of the bunch of shareware
spreadsheet clones. Includes file linking for multiple sheets
and a rich macro language. Popup menus are intuitive and help
screens informative.
Cubecalc. A 3D spreadsheet of the shareware persuasion.
PC-Calc. A competent 123 clone from Jim Button who also brought
us his PC-File and PC-File:DB database shareware packages.
Tutorial finished. Have you registered PC-Learn to receive your
bonus disks? Registration is encouraged. Shareware works on the
honor system! Send $25 to Seattle Scientific Photography,
Department PCL6, PO Box 1506, Mercer Island, WA 98040. Latest
version of PC-Learn and two bonus disks shipped promptly!